import pymysql
from pymysql.cursors import DictCursor
from database import DB_CONFIG, get_db_cursor

# 数据库连接配置
DB_CONFIG = {
    'host': '150.109.6.243',
    'user': 'music_ocr_db',
    'password': 'wZktkPy4X2TpZcCY',
    'db': 'music_ocr_db',
    'charset': 'utf8mb4',
    'cursorclass': DictCursor,
}

def check_table_columns(table_name):
    """检查表的列结构"""
    try:
        with get_db_cursor() as cursor:
            cursor.execute(f"SHOW COLUMNS FROM {table_name}")
            columns = cursor.fetchall()
            
            print(f"=== {table_name}表列 ===")
            for column in columns:
                print(column)
            print("\n")
            
            return len(columns) > 0
    except Exception as e:
        print(f"检查{table_name}表出错: {str(e)}")
        return False

def check_table_exists(table_name):
    """检查表是否存在"""
    try:
        with get_db_cursor() as cursor:
            cursor.execute(f"SHOW TABLES LIKE '{table_name}'")
            exists = cursor.fetchone() is not None
            print(f"表 {table_name} {'存在' if exists else '不存在'}")
            return exists
    except Exception as e:
        print(f"检查{table_name}表是否存在时出错: {str(e)}")
        return False

def create_community_tables():
    """创建社区模块相关的表"""
    try:
        with get_db_cursor(commit=True) as cursor:
            # 创建帖子表
            cursor.execute("""
                CREATE TABLE IF NOT EXISTS community_posts (
                    id INT NOT NULL AUTO_INCREMENT,
                    user_id INT NOT NULL,
                    title VARCHAR(255) NOT NULL,
                    content TEXT NOT NULL,
                    score_upload_id INT NULL,
                    view_count INT DEFAULT 0,
                    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                    post_status ENUM('active', 'deleted') DEFAULT 'active',
                    PRIMARY KEY (id),
                    CONSTRAINT fk_post_user FOREIGN KEY (user_id) REFERENCES users (id),
                    CONSTRAINT fk_post_score FOREIGN KEY (score_upload_id) REFERENCES score_uploads (id) ON DELETE SET NULL
                )
            """)
            
            # 创建评论表
            cursor.execute("""
                CREATE TABLE IF NOT EXISTS post_comments (
                    id INT NOT NULL AUTO_INCREMENT,
                    post_id INT NOT NULL,
                    user_id INT NOT NULL,
                    content TEXT NOT NULL,
                    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                    PRIMARY KEY (id),
                    CONSTRAINT fk_comment_post FOREIGN KEY (post_id) REFERENCES community_posts (id) ON DELETE CASCADE,
                    CONSTRAINT fk_comment_user FOREIGN KEY (user_id) REFERENCES users (id)
                )
            """)
            
            # 创建点赞表
            cursor.execute("""
                CREATE TABLE IF NOT EXISTS post_likes (
                    id INT NOT NULL AUTO_INCREMENT,
                    post_id INT NOT NULL,
                    user_id INT NOT NULL,
                    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                    PRIMARY KEY (id),
                    UNIQUE KEY unique_like (post_id, user_id),
                    CONSTRAINT fk_like_post FOREIGN KEY (post_id) REFERENCES community_posts (id) ON DELETE CASCADE,
                    CONSTRAINT fk_like_user FOREIGN KEY (user_id) REFERENCES users (id)
                )
            """)
            
            # 创建关注表
            cursor.execute("""
                CREATE TABLE IF NOT EXISTS user_follows (
                    id INT NOT NULL AUTO_INCREMENT,
                    follower_id INT NOT NULL,
                    followed_id INT NOT NULL,
                    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                    PRIMARY KEY (id),
                    UNIQUE KEY unique_follow (follower_id, followed_id),
                    CONSTRAINT fk_follower FOREIGN KEY (follower_id) REFERENCES users (id) ON DELETE CASCADE,
                    CONSTRAINT fk_followed FOREIGN KEY (followed_id) REFERENCES users (id) ON DELETE CASCADE
                )
            """)
            
            print("社区模块相关表创建成功")
            return True
    except Exception as e:
        print(f"创建社区模块相关表时出错: {str(e)}")
        return False

if __name__ == "__main__":
    # 检查基础表
    check_table_columns("score_uploads")
    check_table_columns("audio_files")
    
    # 检查社区相关表
    print("\n=== 检查社区模块相关表 ===")
    tables = ["community_posts", "post_comments", "post_likes", "user_follows"]
    missing_tables = []
    
    for table in tables:
        if not check_table_exists(table):
            missing_tables.append(table)
    
    # 如果有缺失的表，提示创建
    if missing_tables:
        print(f"\n缺少以下社区模块表: {', '.join(missing_tables)}")
        create = input("是否创建缺失的表？(y/n): ")
        if create.lower() == 'y':
            create_community_tables()
    else:
        print("\n所有社区模块表都存在")
        # 检查表结构
        for table in tables:
            check_table_columns(table) 